3/9/2008 – Accessing the INSERTED/DELETED tables outside a trigger

If you've ever written a database trigger in SQL Server, you know that the INSERTED/DELETED system tables are great for seeing the contents of rows before and after an update.

Unfortunately, as recently as SQL Server 2000, it wasn't easy getting information from INSERTED/DELETED rows outside of a trigger. As a matter of fact, INSERTED/DELETED tables were only visible inside a trigger. The best you could do was write code inside a trigger, to copy data from INSERTED/DELETED into temp tables, and then read the temp tables outside the trigger. Sure, you could make it work, but it was like eating broccoli – yuck.

(My apologies to all you broccoli-lovers out there)

Well, SQL Server 2005 introduced a new capability called OUTPUT, which you could append to an INSERT/UPDATE/DELETE statement. Essentially, OUTPUT allows you to specify the rows in the INSERTED/DELETED system tables – so you can actually issue an UPDATE statement, and also get values from the INSERTED/DELETED tables as a result set. Sound too good to be true? Well, check out this little sample:

-- Create a test table variable, add three rows to it, and then update the first two
DECLARE @tTest TABLE ( IDCol int identity,Amount decimal(10,2))
INSERT INTO @tTest VALUES (100)
INSERT INTO @tTest VALUES (200)
INSERT INTO @tTest VALUES (300)

UPDATE TOP(2) @tTest SET Amount = Amount * 10
    OUTPUT INSERTED.IDCol, DELETED.Amount as OldAmount, Inserted.Amount as NewAmount

-- will yield the following:
IDCol     OldAmount         NewAmount
1         100.00            1000.00
2         200.00            2000.00

All I've done in the sample above is add an OUTPUT statement to the end of the UPDATE statement, and then I specified the columns. Obviously this is a simple example – but if the table contained calculated columns, or columns affected by a trigger, the OUTPUT of any columns from the INSERTED table would reflect those calculations.

Pretty cool, eh? This means no more round trips back to the server (or at least no more additional SQL statements) to retrieve what changed.

Now, there's one more thing – suppose you wanted to direct the values from INSERTED/DELETED into a table variable. You could the following, to redirect the OUTPUT to a table variable. (This way, I can use OUTPUT for multiple tables)

DECLARE @tResults TABLE (IDCol int, OldAmount decimal(10,2), NewAmount decimal(10,2))

UPDATE TOP(2) @tTest
    SET Amount = Amount * 10
        OUTPUT INSERTED.IDCOL, DELETED.AMOUNT, INSERTED.AMOUNT INTO @tResults

SELECT * FROM @tResults

KG